<%@ page language="java" contentType="text/json; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@page trimDirectiveWhitespaces="true"%>    
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@ page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.text.SimpleDateFormat" %>

<%
	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
			
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
	
	if("admin".equals(htp_session.getAttribute("sUser"))){%>
	<script>alert("This user dont have PERMISSION to access this zone.");</script>
	<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=admin_home.jsp"> 	
	<%}	
	
	Date date = new Date();
	int temp_year = date.getYear()+1900;
	int temp_month = date.getMonth()+1;
	
	String sql_con = request.getParameter("sql_con");
	String start_date_con = request.getParameter("start_date_con");
	String name_con = request.getParameter("name_con");
	boolean year_high = Boolean.parseBoolean(request.getParameter("year_high"));
	System.out.println("@ Background sql_con:"+sql_con);
	System.out.println("@ Background start_date_con:"+start_date_con);
	System.out.println("@ Background name_con:"+name_con);
	
	if(("X").equals(start_date_con))
	{
		
	}
	else{
		if(year_high) {
			sql_con = sql_con +"< '"+start_date_con+"'";
		}
		else {
			sql_con = sql_con +"LIKE '%"+start_date_con+"%'";
		}
	}
	
	if("null".equals(name_con) || null == name_con) {
		
	}
	else {
		sql_con = sql_con +" AND name_th LIKE '%"+name_con+"%'";
	}
	
	
	String Json = "";
	ObjectMapper mapper = new ObjectMapper();
	
	//set Database Connection
		String hostProps = "";
		String usernameProps  = "";
		String passwordProps  = "";
		String databaseProps = "";
		
		try {
			//get current path
			ServletContext servletContext = request.getSession().getServletContext();
			
			InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
			Properties props = new Properties();
			
			props.load(input);

			hostProps  = props.getProperty("host");
			usernameProps  = props.getProperty("username");
			passwordProps  = props.getProperty("password");
			databaseProps = props.getProperty("database");
		} catch (Exception e) { 
			out.println(e);  
		}
	
	// connect database
	Statement statement = null;
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620");//////"&characterEncoding=tis620" Encoding Thai);
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
			
			List<Student> student = new ArrayList<Student>();
			
			System.out.println("Before ExcuteQuery sql_con:"+sql_con);
			ResultSet rs = 
					connect.createStatement().executeQuery(" SELECT S.picture, S.student_id, S.start_date, N.name_th, "+
														   " N.surname_th, D.department_name_th, ST.status_name "+
														   " FROM student S, full_name N, department D, status ST, people P"+
														   " WHERE S.index = P.index AND P.full_name_id = N.full_name_id "+
														   " AND S.department_id = D.department_id AND S.status_id = ST.status_id "+
														    sql_con);
			
			while(rs.next())
			{
				Student stu = new Student();
		
				stu.setPicture(rs.getString("picture"));
				stu.setStudentId(rs.getString("student_id"));
				
				
				
				Date start_date = new SimpleDateFormat("yyyy-MM-dd").parse(rs.getString("start_date"));
				int start_date_num = start_date.getYear()+1900;
				String year_str ;
			
			
				if(temp_month<6)
				{
					
					year_str =(temp_year-start_date_num) + "";
					
				}else{
				
					year_str =(temp_year-start_date_num+1)+"";
				}
				
				
				
				stu.setYear(year_str);
								
				stu.setNameTH(rs.getString("name_th"));
				stu.setSurnameTH(rs.getString("surname_th"));
				stu.setDepartment(rs.getString("department_name_th"));
				stu.setStatus(rs.getString("status_name"));
							
				student.add(stu);
			//	int temp_x = student.size();
			}		
			
			
			try {
				Json = mapper.writeValueAsString(student); 
			} catch (JsonGenerationException ex) {
				ex.printStackTrace();
			} catch (JsonMappingException ex) {
				ex.printStackTrace();
			} catch (IOException ex) {
				ex.printStackTrace();
			}
			
			out.print(Json);
			
			System.out.println("Size of student list :"+ student.size());
			
		
			System.out.println("Json :"+ Json);	
			
			
		} else {
			System.out.println("Database Connect Failed.");	

		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}

	connect.close();
%>